import akshare as ak
from foftact.etf.ETF_MODEL import ETF_MODEL
from foftact.db.dbutil import pooled_db

from datetime import datetime

def collect():
    '''
    ETF 实时行情 列表
    :return:
    '''
    funds_data = ak.fund_etf_spot_em()
    return funds_data

def transform(fund):
    etf = ETF_MODEL()
    etf.code = fund[0]
    etf.name = fund[1]
    etf.update_time = fund[36].to_pydatetime() if fund[36] else datetime(datetime.MINYEAR, 1, 1)

    # print(f'{etf.update_time}')

    etf.valid_date = fund[35].to_pydatetime() if fund[35] else datetime(datetime.MINYEAR, 1, 1)
    etf.net = float(fund[2])
    etf.netagg = float(fund[2])
    # fund_type=fund[6],
    # status_type=fund[7],
    # annually_rate=Decimal(fund[8]),
    etf.amount = float(fund[34])
    return etf

# save to db
def batch_save(etf_list, batch_size = 10, is_print=False):
    # 从连接池获取连接
    conn = pooled_db.connection()
    # 获取游标
    cursor = conn.cursor()
    # etf_list 拆分为10个一组
    for i in range(0, len(etf_list), batch_size):
        batch_etf_list = etf_list[i:i+batch_size]
        # print(f'batch_etf_list: {batch_etf_list}')
        sql_list = list()
        for row in range(0, len(batch_etf_list), 1):
            etf = batch_etf_list[row]
            sql = f'INSERT INTO etf (code, name, update_time, valid_date, net, netagg, amount) VALUES \n' \
                    f' (\'{etf.code}\', \'{etf.name}\', \'{etf.update_time}\', \'{etf.valid_date}\', {etf.net}, {etf.netagg}, {etf.amount}) as etf_row_{row} \n' \
                    f' ON DUPLICATE KEY UPDATE name = etf_row_{row}.name, update_time = etf_row_{row}.update_time,  \n ' \
                    f' valid_date = etf_row_{row}.valid_date,  net = etf_row_{row}.net, netagg = etf_row_{row}.netagg, amount = etf_row_{row}.amount;'
            sql_list.append(sql)
            if is_print:
                print(f'sql: {sql}')

        if len(sql_list) > 0:
            for sql in sql_list:
                result = cursor.execute(sql)
                if is_print:
                    print(f'result: {result}')

            # 执行sql # 提交事务
            conn.commit()
            # 打印影响的行数
            if is_print:
                print(f"{cursor.rowcount} rows were inserted.")

    # 关闭游标和连接
    cursor.close()




